!pip install folium
Requirement already satisfied: folium in c:\users\dell\anaconda3\lib\site-packages (0.12.1) Requirement already satisfied: jinja2>=2.9 in c:\users\dell\anaconda3\lib\site-packages (from folium) (2.11.2) Requirement already satisfied: requests in c:\users\dell\anaconda3\lib\site-packages (from folium) (2.24.0) Requirement already satisfied: numpy in c:\users\dell\anaconda3\lib\site-packages (from folium) (1.19.2) Requirement already satisfied: branca>=0.3.0 in c:\users\dell\anaconda3\lib\site-packages (from folium) (0.4.2) Requirement already satisfied: MarkupSafe>=0.23 in c:\users\dell\anaconda3\lib\site-packages (from jinja2>=2.9->folium) (1.1.1) Requirement already satisfied: certifi>=2017.4.17 in c:\users\dell\anaconda3\lib\site-packages (from requests->folium) (2020.6.20) Requirement already satisfied: chardet<4,>=3.0.2 in c:\users\dell\anaconda3\lib\site-packages (from requests->folium) (3.0.4) Requirement already satisfied: urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1 in c:\users\dell\anaconda3\lib\site-packages (from requests->folium) (1.25.11) Requirement already satisfied: idna<3,>=2.5 in c:\users\dell\anaconda3\lib\site-packages (from requests->folium) (2.10)
!pip install plotly
Requirement already satisfied: plotly in c:\users\dell\anaconda3\lib\site-packages (4.14.3) Requirement already satisfied: retrying>=1.3.3 in c:\users\dell\anaconda3\lib\site-packages (from plotly) (1.3.3) Requirement already satisfied: six in c:\users\dell\anaconda3\lib\site-packages (from plotly) (1.15.0)
import plotly.express as px
import plotly.express as px
import plotly.figure_factory as ff
from plotly.subplots import make_subplots
import folium
import plotly.graph_objects as go
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
%matplotlib inline
import math
import random
from datetime import timedelta
cnf = '#393e46'
dth = '#ff2e63'
rec = '#21bf73'
act = '#fe9801'
import warnings
warnings.filterwarnings('ignore')
import plotly as py
py.offline.init_notebook_mode(connected = True)
import os
df = pd.read_csv('cse.csv', encoding = 'latin')
df.head()
| SR Case Number | CES Date Created | Region | Country Name | Customer Effort Score | CES Why | CES Suggestion | Domain Metier | SR Category | SR Type | ... | Date Creation | Year Creation | Month Creation | Date Resolution | Date Closed | Closing days | Group | CaseOwner | SLA Violation | Age with Pending-External | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 497539 | 16/10/2020 | North America | USA | 1 | I refuse to provide surveys for Alcatel until ... | refuse to provide surveys for Alcatel until th... | TECHNICAL SUPPORT COMMUNICATIONS | Technical Support Communications | Product Support | ... | 29/09/2020 | 2020 | 10 | 06/10/2020 | 17/10/2020 | 11 | LARGE SYSTEM TAC ENGLISH | Venkatesh GOPALRAO | 0 | 7 |
| 1 | 496273 | 16/10/2020 | Europe South | Lebanon | 7 | NaN | NaN | TECHNICAL SUPPORT COMMUNICATIONS | Technical Support Communications | Product Support | ... | 24/09/2020 | 2020 | 10 | 15/10/2020 | 16/10/2020 | 1 | LARGE SYSTEM TAC ENGLISH | Vijaya Marthandan | 1 | 21 |
| 2 | 500170 | 16/10/2020 | Europe North | United Kingdom | 7 | The ALE rep understood and action the requirment. | none. | -- | License Management | License Request | ... | 12/10/2020 | 2020 | 10 | 14/10/2020 | 16/10/2020 | 2 | OTEC_TAC | Gunasekaran MANI | 1 | 2 |
| 3 | 501086 | 16/10/2020 | Europe North | United Kingdom | 7 | As they processed the ESR within a responble t... | None. | TOOLS | License Management | License Request | ... | 15/10/2020 | 2020 | 10 | 16/10/2020 | 16/10/2020 | 0 | E-LICENSING & ORDERING SUPPORT | David DARASSE | 0 | 1 |
| 4 | 500777 | 16/10/2020 | Europe North | Germany | 7 | NaN | NaN | TECHNICAL SUPPORT NETWORK | Technical Support Network | Product Support | ... | 14/10/2020 | 2020 | 10 | 15/10/2020 | 15/10/2020 | 0 | NETWORK SOLUTIONS TEC | Devender DHIMAN | 0 | 1 |
5 rows × 25 columns
df.isna().sum()
SR Case Number 0 CES Date Created 0 Region 0 Country Name 0 Customer Effort Score 0 CES Why 0 CES Suggestion 0 Domain Metier 0 SR Category 0 SR Type 0 SR Product Category 0 Account Name 0 AccountOwner 0 SR Contact Name 0 SR Severity 0 Date Creation 0 Year Creation 0 Month Creation 0 Date Resolution 0 Date Closed 0 Closing days 0 Group 0 CaseOwner 0 SLA Violation 0 Age with Pending-External 0 dtype: int64
df['SR Product Category'].fillna(0, inplace=True)
confirmed = df.groupby('Date Resolution').sum()['Age with Pending-External'].reset_index()
confirmed.head(20)
| Date Resolution | Age with Pending-External | |
|---|---|---|
| 0 | 01/04/2020 | 74 |
| 1 | 01/05/2020 | 1 |
| 2 | 01/06/2020 | 24 |
| 3 | 01/07/2020 | 253 |
| 4 | 01/09/2020 | 14 |
| 5 | 01/10/2019 | 256 |
| 6 | 01/10/2020 | 3 |
| 7 | 01/11/2019 | 11 |
| 8 | 02/01/2020 | 223 |
| 9 | 02/03/2020 | 49 |
| 10 | 02/04/2020 | 39 |
| 11 | 02/06/2020 | 80 |
| 12 | 02/07/2019 | 56 |
| 13 | 02/07/2020 | 2 |
| 14 | 02/08/2019 | 194 |
| 15 | 02/09/2019 | 7 |
| 16 | 02/09/2020 | 22 |
| 17 | 02/10/2019 | 113 |
| 18 | 02/10/2020 | 9 |
| 19 | 02/12/2019 | 87 |
confirmed1 = df.groupby('Date Resolution').sum()['Closing days'].reset_index()
confirmed1.head(20)
| Date Resolution | Closing days | |
|---|---|---|
| 0 | 01/04/2020 | 63 |
| 1 | 01/05/2020 | 0 |
| 2 | 01/06/2020 | 5 |
| 3 | 01/07/2020 | 47 |
| 4 | 01/09/2020 | 1 |
| 5 | 01/10/2019 | 3 |
| 6 | 01/10/2020 | 10 |
| 7 | 01/11/2019 | 4 |
| 8 | 02/01/2020 | 21 |
| 9 | 02/03/2020 | 28 |
| 10 | 02/04/2020 | 81 |
| 11 | 02/06/2020 | 21 |
| 12 | 02/07/2019 | 3 |
| 13 | 02/07/2020 | 11 |
| 14 | 02/08/2019 | 7 |
| 15 | 02/09/2019 | 1 |
| 16 | 02/09/2020 | 0 |
| 17 | 02/10/2019 | 1 |
| 18 | 02/10/2020 | 13 |
| 19 | 02/12/2019 | 4 |
fig = go.Figure()
fig.add_trace(go.Scatter(x= df['Closing days'], y = df['Date Resolution']))
fig.show() # mostly case closing days are mostly before 10 days
fig = go.Figure()
fig.add_trace(go.Scatter(x= df['Closing days'], y = df['CaseOwner']))
fig.show() # mostly case closing days are mostly before 10 days
fig = go.Figure()
fig.add_trace(go.Scatter(x= df['Closing days'], y = df['SR Type']))
fig.show() # mostly case closing days are mostly before 10 days
temp = df.groupby('CaseOwner')['Closing days','Customer Effort Score','SR Category'].sum()
temp
| Closing days | Customer Effort Score | |
|---|---|---|
| CaseOwner | ||
| Abdelhaq MEJDOUB | 116 | 6 |
| Agata KULA | 36 | 132 |
| Aijaz Ahmad ITOO | 0 | 13 |
| Ajith VALLEPARAMBILSURENDRAN | 0 | 25 |
| Akshay NAROTTAM VARIA | 14 | 48 |
| ... | ... | ... |
| Viviane BUDALY | 5 | 7 |
| Yannick GUILLEMOT | 5 | 6 |
| Yasir ATHIKKAL PANDIKASALAYIL | 27 | 62 |
| Yogendran KUPPUSWAMY | 0 | 70 |
| Zuzanna SZYMCZAK | 0 | 7 |
262 rows × 2 columns
rate = df.groupby('Date Resolution').sum()
rate
| SR Case Number | Customer Effort Score | Year Creation | Month Creation | Closing days | Age with Pending-External | |
|---|---|---|---|---|---|---|
| Date Resolution | ||||||
| 01/04/2020 | 3205620 | 48 | 14140 | 29 | 63 | 74 |
| 01/05/2020 | 466317 | 7 | 2020 | 5 | 0 | 1 |
| 01/06/2020 | 940363 | 14 | 4040 | 12 | 5 | 24 |
| 01/07/2020 | 4258278 | 52 | 18180 | 63 | 47 | 253 |
| 01/09/2020 | 1962700 | 28 | 8080 | 36 | 1 | 14 |
| ... | ... | ... | ... | ... | ... | ... |
| 31/05/2020 | 465863 | 6 | 2020 | 6 | 5 | 33 |
| 31/07/2019 | 1567900 | 27 | 8076 | 29 | 1 | 203 |
| 31/07/2020 | 2897848 | 41 | 12120 | 45 | 13 | 73 |
| 31/08/2020 | 1947372 | 28 | 8080 | 32 | 0 | 86 |
| 31/10/2019 | 1694409 | 27 | 8076 | 40 | 0 | 44 |
381 rows × 6 columns
df1 = pd.read_csv('tap.csv', encoding = 'latin')
df1.head()
| Case _owner | Very strong Disagree | Strongly Disagree | Disagree | Agree or Disagree | Agree | Strongly Agree | Very strongly Agree | Unnamed: 8 | Unnamed: 9 | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | David DARASSE | 1.0 | 0.0 | 0.0 | 3.0 | 2.0 | 10.0 | 70.0 | NaN | NaN |
| 1 | NaN | 1.0 | NaN | NaN | 3.0 | 1.0 | 18.0 | 58.0 | NaN | NaN |
| 2 | NaN | 2.0 | 3.0 | 1.0 | 6.0 | 3.0 | 19.0 | 48.0 | NaN | NaN |
| 3 | NaN | 1.0 | 2.0 | 1.0 | 4.0 | 2.0 | 12.0 | 47.0 | NaN | NaN |
| 4 | Audrey BIEBER | 0.0 | 1.0 | 0.0 | 3.0 | 1.0 | 8.0 | 42.0 | NaN | NaN |
# Customer Effort Score Year 7 -very strongly agree and 1 very strongly disagree
df['CaseOwner'].value_counts().head(10).plot.bar()
<AxesSubplot:>
df['CaseOwner'].value_counts()
Generic WELCOME 147
David DARASSE 86
Audrey BIEBER 55
Christine WANDREY 34
Irene MORIANO 32
...
Sauveur BARUCH 1
Marc BEAURAIN 1
Jonathan Dudley 1
Frederic LEMMEL 1
Pan YI 1
Name: CaseOwner, Length: 262, dtype: int64
df.columns
Index(['SR Case Number', 'CES Date Created', 'Region', 'Country Name',
'Customer Effort Score', 'CES Why', 'CES Suggestion', 'Domain Metier',
'SR Category', 'SR Type', 'SR Product Category', 'Account Name',
'AccountOwner', 'SR Contact Name', 'SR Severity', 'Date Creation',
'Year Creation', 'Month Creation', 'Date Resolution', 'Date Closed',
'Closing days', 'Group', 'CaseOwner', 'SLA Violation',
'Age with Pending-External'],
dtype='object')
fig = px.bar(df, x= 'CaseOwner', y = 'Country Name', color = 'CaseOwner', height = 600,
title = 'caseowners vs country ', color_discrete_sequence= px.colors.cyclical.mygbm)
fig.show()
fig = px.bar(df, x= 'CaseOwner', y = 'Date Resolution', color = 'Date Resolution', height = 600,
title = 'caseowners vs Date Resolution', color_discrete_sequence= px.colors.cyclical.mygbm)
fig.show()
fig = px.bar(df, x= 'CaseOwner', y = 'Date Resolution', color = 'Date Resolution', height = 600,
title = 'caseowners vs Date Resolution', color_discrete_sequence= px.colors.cyclical.mygbm)
fig.show()
fig = px.line(df, x= 'CaseOwner', y = 'Date Resolution', color = 'Date Resolution', height = 600,
title = 'caseowners vs Date Resolution', color_discrete_sequence= px.colors.cyclical.mygbm)
fig.show()
# Top 30 Case owners
df= pd.read_csv('product.csv')
df.head(5)
| caseowner | products handled | |
|---|---|---|
| 0 | Generic WELCOME | 140 |
| 1 | David DARASSE | 83 |
| 2 | Audrey BIEBER | 53 |
| 3 | Christine WANDREY | 34 |
| 4 | Irene MORIANO | 32 |
import pandas as pd
import seaborn as sns # Why sns? It's a reference to The West Wing
import matplotlib.pyplot as plt # seaborn is based on matplotlib
sns.set(color_codes=True) # adds a nice background to the graphs
%matplotlib inline
# tells python to actually display the graphs
df.head(40)
| caseowner | products handled | |
|---|---|---|
| 0 | Generic WELCOME | 140 |
| 1 | David DARASSE | 83 |
| 2 | Audrey BIEBER | 53 |
| 3 | Christine WANDREY | 34 |
| 4 | Irene MORIANO | 32 |
| 5 | Jegen DORASAMI | 29 |
| 6 | Sandrine JUNG | 27 |
| 7 | Athirai AJEETHKUMARMANJULA | 26 |
| 8 | Paari PANNEERSELVAM | 25 |
| 9 | Parash PAGOOAH | 23 |
| 10 | Gunasekaran MANI | 22 |
| 11 | Katarina STADLER | 22 |
| 12 | Aravindan MANOHAR | 21 |
| 13 | Hislamar BRACHO | 20 |
| 14 | Kesaw KUMAR | 19 |
| 15 | Agata KULA | 18 |
| 16 | Bruno FLOCH | 18 |
| 17 | Marc BERLING | 18 |
| 18 | Stephane LE MEN | 18 |
| 19 | Monisha RAJENDRAN | 17 |
| 20 | Muhammad UZAIR MAMOOJEE | 17 |
| 21 | Samuel CHANDRASEKARAN | 17 |
| 22 | Boobalan SUBRAMANI | 16 |
| 23 | Kishan SIVADAS | 16 |
| 24 | Arvind DOWLUT | 15 |
| 25 | Iyyappan LAKSHMANAN | 15 |
| 26 | Neethu Chenthamarai | 15 |
| 27 | Ramakrishna Kalagara | 15 |
| 28 | Luis Rosales | 14 |
df= pd.read_csv('sev.csv')
df.head(40)
| Case owner | SRSeverity | Unnamed: 2 | Unnamed: 3 | Unnamed: 4 | Unnamed: 5 | Total | |
|---|---|---|---|---|---|---|---|
| 0 | NaN | NaN | S1-Critical | S2-High | S3-Medium | S4-Low | NaN |
| 1 | Abdelhaq MEJDOUB | 0.0 | 0 | 0 | 0 | 1 | 1.0 |
| 2 | Agata KULA | 0.0 | 0 | 16 | 1 | 1 | 18.0 |
| 3 | Aijaz Ahmad ITOO | 0.0 | 0 | 0 | 0 | 1 | 1.0 |
| 4 | Ajith VALLEPARAMBILSURENDRAN | 0.0 | 0 | 0 | 0 | 4 | 4.0 |
| 5 | Akshay NAROTTAM VARIA | 0.0 | 0 | 0 | 1 | 6 | 7.0 |
| 6 | Alain BOTTI | 0.0 | 0 | 0 | 2 | 1 | 3.0 |
| 7 | Albert DHANARAJ | 0.0 | 0 | 0 | 0 | 1 | 1.0 |
| 8 | Alice ROUSSELOT | 0.0 | 0 | 0 | 4 | 9 | 13.0 |
| 9 | Amar HARPALANI | 0.0 | 0 | 0 | 2 | 8 | 10.0 |
| 10 | Ambalatharasan GNANAPRAKASAM | 0.0 | 0 | 0 | 1 | 5 | 6.0 |
| 11 | Ana VENEGAS | 0.0 | 0 | 1 | 0 | 0 | 1.0 |
| 12 | Andre JARNOUX | 0.0 | 0 | 0 | 0 | 1 | 1.0 |
| 13 | Ankush Keche | 0.0 | 0 | 0 | 0 | 2 | 2.0 |
| 14 | Anne PELLETIER | 0.0 | 0 | 0 | 0 | 4 | 4.0 |
| 15 | Annie ARMANDO | 0.0 | 1 | 0 | 0 | 0 | 1.0 |
| 16 | Anu ULAGANATHAN | 0.0 | 0 | 0 | 1 | 0 | 1.0 |
| 17 | Aravindan MANOHAR | 0.0 | 0 | 0 | 6 | 15 | 21.0 |
| 18 | Arvind DOWLUT | 0.0 | 0 | 0 | 4 | 11 | 15.0 |
| 19 | Ashvin CHUMMUN | 0.0 | 0 | 0 | 0 | 3 | 3.0 |
| 20 | Athirai AJEETHKUMARMANJULA | 0.0 | 0 | 0 | 3 | 23 | 26.0 |
| 21 | Audrey BIEBER | 0.0 | 2 | 47 | 2 | 2 | 53.0 |
| 22 | Aurelie PARISET | 0.0 | 0 | 5 | 1 | 0 | 6.0 |
| 23 | Aurelie Schmoderer | 0.0 | 0 | 0 | 0 | 2 | 2.0 |
| 24 | Aurelien CLARISSE | 0.0 | 0 | 0 | 2 | 7 | 9.0 |
| 25 | Ayoub EL-HABHOUB | 0.0 | 0 | 0 | 1 | 0 | 1.0 |
| 26 | Badr Eddine ATTALBI ALAMI | 0.0 | 0 | 0 | 2 | 0 | 2.0 |
| 27 | Bala CHANDRASEKHAR | 0.0 | 0 | 0 | 3 | 5 | 8.0 |
| 28 | Balaji TANDABANY | 0.0 | 0 | 0 | 4 | 7 | 11.0 |